﻿using System;
using System.Diagnostics;

namespace EtEnvironment
{
    public struct RANGE
    {
        public Int32 nBeginSheet;
        public Int32 nBeginRow;
        public Int32 nBeginCol;
        public Int32 nEndSheet;
        public Int32 nEndRow;
        public Int32 nEndCol;
        public bool IsValid()
        {
            bool b1 = 0 <= nBeginRow && nBeginRow <= nEndRow &&
                nEndRow < gVal.MAX_ROWS_COUNT || 0 == nBeginRow && -1 == nEndRow;
            bool b2 = 0 <= nBeginCol && nBeginCol <= nEndCol &&
                nEndCol < gVal.MAX_COLS_COUNT || 0 == nBeginCol && -1 == nEndCol;
            if (b1 && b2)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public bool IsContain(ref RANGE rgx)
        {
            if (!IsValid() || !rgx.IsValid())
            {
                Debug.Assert(false);
                return false;
            }

            int nRowEndThis = ValidateWhole(nEndRow, gVal.MAX_ROWS_COUNT-1);
            int nColEndThis = ValidateWhole(nEndCol, gVal.MAX_COLS_COUNT - 1);
            int nRowEndX = ValidateWhole(rgx.nEndRow, gVal.MAX_ROWS_COUNT - 1);
            int nColEndX = ValidateWhole(rgx.nEndCol, gVal.MAX_COLS_COUNT - 1);
            return nBeginSheet <= rgx.nBeginSheet && rgx.nEndSheet <= nEndSheet &&
                nBeginRow <= rgx.nBeginRow && nRowEndX <= nRowEndThis &&
                nBeginCol <= rgx.nBeginCol && nColEndX <= nColEndThis;
        }

        public bool IsContain(ref CELL cellX)
        {
            if (!IsValid() || !cellX.IsValid())
            {
                Debug.Assert(false);
                return false;
            }

            int nRowEndThis = ValidateWhole(nEndRow, gVal.MAX_ROWS_COUNT - 1);
            int nColEndThis = ValidateWhole(nEndCol, gVal.MAX_COLS_COUNT - 1);
            return nBeginSheet <= cellX.nSheet && cellX.nSheet <= nEndSheet &&
                nBeginRow <= cellX.nRow && cellX.nRow <= nRowEndThis &&
                nBeginCol <= cellX.nCol && cellX.nCol <= nColEndThis;
        }

        public RANGE ExpandToCells()
        {
            RANGE x = new RANGE();

            x.nBeginSheet = nBeginSheet;
            x.nEndSheet = nEndSheet;
            x.nBeginRow = nBeginRow;
            x.nEndRow = ValidateWhole(nEndRow, gVal.MAX_ROWS_COUNT-1);
            x.nBeginCol = nBeginCol;
            x.nEndCol = ValidateWhole(nEndCol, gVal.MAX_COLS_COUNT-1);

            return x;
        }

        private static Int32 ValidateWhole(Int32 x, Int32 y)
        {
            return (x == -1) ? y : x;
        }
    }
    
    public struct CELL
    {
        public int nRow;
        public int nCol;
        public int nSheet;
        public override String ToString()
        {
            String strTmp = "Sheet-" + nSheet + " ";
            strTmp += "Row-" + nRow + " ";
            strTmp += "Column-" + nCol;
            return strTmp;
        }

        public Boolean IsValid()
        {
            return 0 <= nRow && nRow < gVal.MAX_ROWS_COUNT &&
                0 <= nCol && nCol < gVal.MAX_COLS_COUNT &&
                0 <= nSheet;
        }
    }

    public static class gVal
    {
        public static String[] GetErrorStrings()
        {
            return new String[]{
                "#VALUE!", "#REF!", "#NULL!", "#NAME?", "#NUM!", "#N/A", "#DIV/0!"
            };
        }

        public const Int32 MAX_ROWS_COUNT = 65536;
        public const Int32 MAX_COLS_COUNT = 256;
        public const Int32 BLK_ROW_COUNT = 32;
        public const Int32 BLK_COL_COUNT = 16;
    }

    class RangeConverter
    {
        private enum DecodeRefCate { DR_WHOLE, DR_BEGIN, DR_END };

        public RangeConverter(String[] strSheets)
        {
            m_StrSheets = strSheets;
        }

        public RANGE Str2Range(String strRg)
        {
            RANGE rg;
            String[] tmps = strRg.Split(new char[] { ':' });
            if (tmps.Length == 1)
            {
                Int32[] pos = Str2Cell(tmps[0], DecodeRefCate.DR_WHOLE);
                Debug.Assert(pos.Length == 3);
                rg.nBeginSheet = rg.nEndSheet = pos[0];
                rg.nBeginRow = rg.nEndRow = pos[1];
                rg.nBeginCol = rg.nEndCol = pos[2];
            }
            else if (tmps.Length == 2 || tmps.Length == 3)
            {
                Int32 x = tmps.Length - 2;
                Int32[] posBegin = Str2Cell(tmps[x], DecodeRefCate.DR_BEGIN);
                Int32[] posEnd = Str2Cell(tmps[x + 1], DecodeRefCate.DR_END);
                Debug.Assert(posBegin.Length == 3);
                Debug.Assert(posEnd.Length == 3);
                rg.nBeginSheet = rg.nEndSheet = posBegin[0];
                rg.nBeginRow = posBegin[1]; rg.nEndRow = posEnd[1];
                rg.nBeginCol = posBegin[2]; rg.nEndCol = posEnd[2];
                if (x == 1)
                {
                    rg.nBeginSheet = DecodeSheet(tmps[0]);
                }
            }
            else
            {
                throw new Exception("String to Range Error:too much colon(:)");
            }

            return rg;
        }

        public String Range2Str(RANGE rg)
        {
            if (!rg.IsValid())
            {
                throw new Exception("Range to String Error: range is Invalid");
            }

            String strRet;
            if (rg.nBeginSheet == rg.nEndSheet)
            {
                strRet = DecorateSheet(m_StrSheets[rg.nBeginSheet]) + "!";
            }
            else
            {
                String xb = DecorateSheet(m_StrSheets[rg.nBeginSheet]);
                String xe = DecorateSheet(m_StrSheets[rg.nEndSheet]);
                if (xb[0] == '\'' || xe[0] == '\'')
                {
                    xb = (xb[0] == '\'') ? xb.Substring(0, xb.Length - 1) : xb;
                    xe = (xe[0] == '\'') ? xe.Substring(1, xe.Length - 1) : xe;
                }
                strRet = xb + ":" + xe + "!";
            }

            if (rg.nBeginRow == 0 && (rg.nEndRow + 1) % gVal.MAX_ROWS_COUNT == 0)
            {
                strRet += DecodeColumn(rg.nBeginCol) + ':' + DecodeColumn(rg.nEndCol);
            }
            else if (rg.nBeginCol == 0 && (rg.nEndCol + 1) % gVal.MAX_COLS_COUNT == 0)
            {
                strRet += (rg.nBeginRow + 1).ToString() + ':' + (rg.nEndRow + 1).ToString();
            }
            else
            {
                strRet += DecodeColumn(rg.nBeginCol) + (rg.nBeginRow + 1).ToString() + ':' +
                     DecodeColumn(rg.nEndCol) + (rg.nEndRow + 1).ToString();
            }

            return strRet;
        }

        public String Range2CellStr(RANGE rg, bool bLeftTop, bool bSheet)
        {
            CELL cell = new CELL();
            if (bLeftTop)
            {
                cell.nSheet = rg.nBeginSheet;
                cell.nRow = rg.nBeginRow;
                cell.nCol = rg.nBeginCol;
            }
            else
            {
                cell.nSheet = rg.nEndSheet;
                cell.nRow = rg.nEndRow;
                cell.nCol = rg.nEndCol;
            }

            return Cell2String(cell, bSheet);
        }

        public String Cell2String(CELL cellX, bool bSheet)
        {
            String strRet = "";
            if (bSheet)
            {
                strRet = DecorateSheet(m_StrSheets[cellX.nSheet]) + "!";
            }

            return strRet + DecodeColumn(cellX.nCol) + (cellX.nRow + 1).ToString();
        }

        private String DecodeColumn(Int32 nCol)
        {
            nCol = (nCol + gVal.MAX_COLS_COUNT) % gVal.MAX_COLS_COUNT;
            String strTmp = "" + (Char)('A' + nCol % 26);
            nCol /= 26;
            while (nCol != 0)
            {
                strTmp = (Char)('A' + nCol % 26 - 1) + strTmp;
                nCol /= 26;
            }

            return strTmp;
        }

        private String DecorateSheet(String strSheet)
        {
            char[] needDecorate = new char[] {' ', '!', '"', '#', '$', '%','&', '\'',
                '(', ')', '+', ',','-', ';', '<', '=', '>', '@','^',
                '`', '{', '|', '}', '~','　', '！', '＂', '＃', '＄', '％','＆', '＇',
                '（', '）', '＋', '，','－', '；', '＜', '＝', '＞', '＠','＾', '｀', 
                '｛', '｜', '｝', '～'};

            char[] needEscape = new char[] { '\'', '＇', '’' };
            bool bx = false;
            try
            {
                Int32 nDummy1 = -1, nDummy2 = -1;
                DecodeA1Ref(strSheet, ref nDummy1, ref nDummy2, DecodeRefCate.DR_WHOLE);
            }
            catch (System.Exception)
            {
                bx = true;
            }
            if (strSheet.IndexOfAny(needDecorate) == -1 && !Char.IsDigit(strSheet[0]) &&
                strSheet.ToLower() != "true" && strSheet.ToLower() != "false" && bx)
            {
                return strSheet;
            }
            else if (strSheet.IndexOfAny(needEscape) == -1)
            {
                return "'" + strSheet + "'";
            }
            else
            {
                String strTmp = "'";
                foreach (char ch1 in strSheet.ToCharArray())
                {
                    if (Array.IndexOf<char>(needEscape, ch1) != -1)
                    {
                        strTmp += '\'';
                    }
                    strTmp += ch1;
                }

                return strTmp + "'";
            }
        }

        private Int32[] Str2Cell(String strCell, DecodeRefCate drc)
        {
            String[] tmps = strCell.Split(new char[] { '!' });
            if (tmps.Length == 1)
            {
                Int32 nRow = 0, nCol = 0;
                DecodeA1Ref(tmps[0], ref nRow, ref nCol, drc);
                return new Int32[] { 0, nRow, nCol };
            }
            else
            {
                if (drc == DecodeRefCate.DR_END)
                {
                    throw new Exception("String to Range Error: too much excalmatory(!)");
                }

                Int32 nRow = 0, nCol = 0;
                String strA1Ref = tmps[tmps.Length - 1];
                DecodeA1Ref(strA1Ref, ref nRow, ref nCol, drc);
                String strSheet = strCell.Substring(0, strCell.Length - strA1Ref.Length - 1);
                return new Int32[] { DecodeSheet(strSheet), nRow, nCol };
            }
        }

        private void DecodeA1Ref(String strA1Ref, ref Int32 nRow, ref Int32 nCol, DecodeRefCate drc)
        {
            if (strA1Ref.Length == 0)
            {
                throw new Exception("String to Range Error: empty string");
            }

            nRow = -1; nCol = -1;

            char[] tmpChars = strA1Ref.ToLower().ToCharArray();
            Int32 nCur = 0;
            while (nCur < tmpChars.Length &&
                Char.IsLetter(tmpChars[nCur]) && nCol < gVal.MAX_COLS_COUNT * 11)
            {
                Int32 x = (tmpChars[nCur++] - 'a');
                nCol = (nCol == -1) ? x : ((nCol + 1) * 26 + x);
            }

            while (nCur < tmpChars.Length &&
                Char.IsDigit(tmpChars[nCur]) && nRow < gVal.MAX_ROWS_COUNT * 11)
            {
                Int32 x = (strA1Ref[nCur++] - '1');
                nRow = (nRow == -1) ? x : ((nRow + 1) * 10 + x);
            }

            if (nCur != tmpChars.Length)
            {
                throw new Exception("String to Range Error: a1 refer convert invalid charactor");
            }

            if (drc == DecodeRefCate.DR_WHOLE && (nRow == -1 || nCol == -1))
            {
                throw new Exception("String to Range Error: a1 refer convert invalid");
            }

            if (nRow == -1)
            {
                nRow = drc == DecodeRefCate.DR_BEGIN ? 0 : (gVal.MAX_ROWS_COUNT - 1);
            }

            if (nCol == -1)
            {
                nCol = drc == DecodeRefCate.DR_BEGIN ? 0 : (gVal.MAX_COLS_COUNT - 1);
            }

            if (nRow < 0 || gVal.MAX_ROWS_COUNT <= nRow || nCol < 0 || gVal.MAX_COLS_COUNT <= nCol)
            {
                throw new Exception("String to Range Error: row or col exceed");
            }
        }

        private Int32 FindSheetStr(String strSheet)
        {
            for (Int32 i = 0; i < m_StrSheets.Length; ++i)
            {
                if (m_StrSheets[i].ToLower() == strSheet.ToLower())
                {
                    return i;
                }
            }

            return -1;
        }

        private Int32 DecodeSheet(String strSheet)
        {
            Int32 x = FindSheetStr(strSheet);

            if (strSheet.Length == 0)
            {
                throw new Exception("String to Range Error: sheet name empty");
            }

            if (x == -1 && strSheet[0] == '\'' &&
                strSheet.Length > 1 && strSheet[strSheet.Length - 1] == '\'')
            {
                strSheet = strSheet.Substring(1, strSheet.Length - 2);
                x = FindSheetStr(strSheet);
            }

            if (x == -1)
            {
                String strTmp = "";
                bool bPre = false;
                foreach (char ch in strSheet.ToCharArray())
                {
                    if (bPre)
                    {
                        bPre = false;
                        strTmp += ch;
                    }
                    else if (ch == '\'')
                    {
                        bPre = true;
                    }
                    else
                    {
                        strTmp += ch;
                    }
                }
                x = FindSheetStr(strTmp);
            }

            if (x == -1)
            {
                throw new Exception("String to Range Error: sheet name not find");
            }

            return x;
        }

        private String[] m_StrSheets;
    }
}